﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Text.RegularExpressions;

namespace nHydrate.DataImport.MySqlClient
{
	public class SchemaModelHelper : ISchemaModelHelper
	{
		#region Public Methods
		
		public bool IsValidConnectionString(string connectionString)
		{
			var valid = false;
			var conn = new MySql.Data.MySqlClient.MySqlConnection();
			try
			{
				conn.ConnectionString = connectionString;
				conn.Open();
				valid = true;
			}
			catch (Exception ex)
			{
				valid = false;
			}
			finally
			{
				conn.Close();
			}
			return valid;
		}

		public bool IsSupportedSQLVersion(string connectionString)
		{
			return true;
		}

		public SQLServerTypeConstants GetSQLVersion(string connectionString)
		{
			return SQLServerTypeConstants.SQL2008;
		}
		
		#endregion

		internal static string GetSqlDatabaseTables()
		{
			var sb = new StringBuilder();
			sb.AppendLine("show full tables where Table_type = 'BASE TABLE'");
			return sb.ToString();
		}

		internal static string GetSqlColumnsForTable(string tableName)
		{
			return "show columns in `" + tableName + "`";
		}

		internal static string GetSqlForRelationships(string tableName)
		{
			var sb = new StringBuilder();
			sb.AppendLine("select TC.CONSTRAINT_NAME, TC.Table_name, column_name, referenced_table_name, referenced_column_name, ordinal_position");
			sb.AppendLine("from INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC inner join ");
			sb.AppendLine("    INFORMATION_SCHEMA.KEY_COLUMN_USAGE KC on TC.table_name = KC.table_name");
			sb.AppendLine("where TC.CONSTRAINT_TYPE = 'FOREIGN KEY' and ");
			sb.AppendLine("    TC.CONSTRAINT_NAME = KC.CONSTRAINT_NAME and");
			sb.AppendLine("    REFERENCED_TABLE_NAME is not null and");
			sb.AppendLine("    TC.table_name = '" + tableName + "' ");
			sb.AppendLine("order by TC.CONSTRAINT_NAME, ordinal_position");
			return sb.ToString();
		}

		internal static string GetSqlIndexesForTable(string tableName)
		{
			return "show indexes in `" + tableName + "`";
		}

		internal static string GetSqlForViews()
		{
			return "SHOW FULL TABLES WHERE TABLE_TYPE LIKE 'VIEW'";
		}

		internal static string GetViewBody(string connectionString, string viewName)
		{
			var sb = new StringBuilder();
			var ds = DatabaseHelper.ExecuteDataset(connectionString, "SHOW CREATE VIEW `" + viewName + "`");
			if (ds.Tables.Count > 0)
			{
				foreach (DataRow dr in ds.Tables[0].Rows)
				{
					var text = (string)dr[1];
					var index = text.IndexOf(" SQL SECURITY ");
					if (index == -1) return string.Empty;
					text = text.Substring(index, text.Length - index);
					index = text.IndexOf(" AS ");
					if (index == -1) return string.Empty;
					text = text.Substring(index + 4, text.Length - index - 4);
					return text;
				}
			}
			return string.Empty;
		}

		internal static string GetSqlForViewsColumns(string viewName)
		{
			return "select * from INFORMATION_SCHEMA.COLUMNS where table_name = '" + viewName + "'";
		}

		internal static string GetSqlForFunctions()
		{
			return "SHOW FUNCTION STATUS";
		}

		internal static string GetFunctionBody(string name, string connectionString)
		{
			var sb = new StringBuilder();
			var ds = DatabaseHelper.ExecuteDataset(connectionString, "SHOW CREATE FUNCTION '" + name + "'");
			if (ds.Tables.Count == 1 && ds.Tables[0].Rows.Count == 1)
			{
				var t = (string)ds.Tables[0].Rows[0][2] + string.Empty;
				var index= t.IndexOf(" RETURNS  ") ;
				if (index!=-1)
				{
					t = t.Substring(index + 1, t.Length - index - 1);
					return t;
				}
			}
			return string.Empty;
		}

		internal static string GetSqlForStoredProceduresParameters(string procName, string dbName)
		{
			return "select * from INFORMATION_SCHEMA.Parameters where SPECIFIC_NAME = '" + procName + "' and SPECIFIC_SCHEMA = '" + dbName + "'";
		}

		internal static string GetSqlForStoredProceduresColumns(string name)
		{
			var sb = new StringBuilder();
			//System.Windows.Forms.Application.DoEvents();
			//sb.AppendLine("SET FMTONLY ON");
			//sb.Append("EXEC [" + (string.IsNullOrEmpty(sp.Schema) ? "dbo" : "sp.Schema") + "].[" + sp.Name + "] ");

			//foreach (var parameter in sp.ParameterList)
			//{
			//  if (parameter.DataType == SqlDbType.UniqueIdentifier)
			//    sb.Append("@" + parameter.Name + "='540C6D43-5645-40FB-980F-2FF126BFBD5E'");
			//  else if (parameter.IsTextType())
			//    sb.Append("@" + parameter.Name + "=''");
			//  else if (parameter.IsNumericType())
			//    sb.Append("@" + parameter.Name + "=0");
			//  else if (parameter.IsBinaryType())
			//    sb.Append("@" + parameter.Name + "=0x0");
			//  else if (parameter.DataType == SqlDbType.Bit)
			//    sb.Append("@" + parameter.Name + "=0");
			//  else if (parameter.IsDateType())
			//    sb.Append("@" + parameter.Name + "='2000-01-01'");
			//  else
			//    System.Diagnostics.Debug.Write(string.Empty);

			//  if (sp.ParameterList.IndexOf(parameter) < sp.ParameterList.Count - 1)
			//    sb.Append(", ");
			//}

			//sb.AppendLine();
			return sb.ToString();
		}

		internal static string GetSqlForStoredProceduresBody(string spName, string connectionString)
		{
			var sb = new StringBuilder();
			var ds = DatabaseHelper.ExecuteDataset(connectionString, "SHOW CREATE PROCEDURE `" + spName + "`");
			if (ds.Tables.Count > 0)
			{
				foreach (DataRow dr in ds.Tables[0].Rows)
				{
					var arr = ((string)dr[2]).Split('\n');
					var started = false;
					foreach(var s in arr)
					{
						var line = s.ToLower().Replace("\r", string.Empty);
						if (line == "begin" || line.Replace(" ", string.Empty) == "proc:begin")
						{
							started = true;
						}
						else if (line == "end")
						{
							break;
						}
						else if (started)
						{
							sb.Append(s.Replace("\r", string.Empty));
						}
					}
				}
			}
			return sb.ToString().Trim();
		}

		internal static string GetSqlForStoredProcedures(string dbName)
		{
			return "SHOW PROCEDURE STATUS where Db = '" + dbName + "'";
		}

	}
}